package com.demo2do.fms.admin.entity.report.report;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 *
 * @author Ark
 *
 */
public class DriverAccumulatedMileageReportSearch implements QueryBuilder {

    /** 运输公司 */
    private Long organizationId;
    
    /** 驾驶员 */
    private Long driverId;
    
    /** 派车单调度指令开始日期 */
    private String beginDispatchDate;
    
    /** 派车单调度指令截止日期 */
    private String endDispatchDate;
    
    /** 报销开始日期 */
    private String beginReimburseDate;
    
    /** 报销结束日期 */
    private String endReimburseDate;
    
    /** 起始行 */
    private Integer beginIndex;
    
    /** 截止行 */
    private Integer endIndex;

    private QueryFilter queryFilter;

    private QueryOrder queryOrder;

    /**
     * The default constructor
     */
    public DriverAccumulatedMileageReportSearch() {
        queryOrder = new QueryOrder();
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter();
            
            if (StringUtils.isNotBlank(beginDispatchDate)) {
                queryFilter.addSentence("A.DISPATCH_DATE >= TO_DATE('" + beginDispatchDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endDispatchDate)) {
                queryFilter.addSentence("A.DISPATCH_DATE < TO_DATE('" + endDispatchDate.replace("-", "") + "', 'yyyymmdd') + TO_DSINTERVAL ('001 00:00:00')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(beginReimburseDate)) {
                queryFilter.addSentence("C.EXPENSE_REIMBURSE_DATE >= TO_DATE('" + beginReimburseDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endReimburseDate)) {
                queryFilter.addSentence("C.EXPENSE_REIMBURSE_DATE < TO_DATE('" + endReimburseDate.replace("-", "") + "', 'yyyymmdd') + TO_DSINTERVAL ('001 00:00:00')", Collections.emptyMap());
            }
            
            queryFilter.addEquals("C", "ORGANIZATION_ID", organizationId);
            
        }
        
        return queryFilter;
        
    }
    
    /**
     * @return
     */
    public String buildCountTemplate() {
        
        StringBuffer buffer = new StringBuffer(1024);
        
        buffer.append("SELECT * FROM (")
                .append("SELECT ID, ")
                  .append("CASE WHEN (ID IS NULL AND DRIVER IS NOT NULL) THEN '小计' WHEN (ID IS NULL AND DRIVER IS NULL) THEN '总计' ELSE DRIVER END AS driver, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(ORGANIZATION_NAME) END AS organizationName, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(DISPATCH_ORDER_CODE) END AS dispatchOrderCode, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE TO_CHAR(MIN(DISPATCH_DATE), 'YYYY-MM-DD') END AS dispatchDate, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(VEHICLE_NAME) END AS vehicleName, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(START_FACILITY_NAME) END AS startFacilityName, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(END_FACILITY_NAME) END AS endFacilityName, ")
                  .append("SUM(FULL_LOAD_DISTANCE) AS fullLoadDistance, ")
                  .append("SUM(EMPTY_LOAD_DISTANCE) AS emptyLoadDistance, ")
                  .append("SUM(ACTUAL_LOAD_DISTANCE) AS actualLoadDistance, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE SUM(FULL_SALARY) END AS fullSalary, ")
                  .append("CASE WHEN ID IS NULL THEN NULL ELSE SUM(EMPTY_SALARY) END AS emptySalary, ")
                  .append("SUM(DRIVER_SALARY) AS driverSalary ")
           .append("FROM (SELECT * FROM ( ")
                   .append("(SELECT A.ID, ")
                           .append("A.MAIN_DRIVER_ID AS DRIVER_ID, ")
                           .append("D.ORGANIZATION_NAME AS ORGANIZATION_NAME, ")
                           .append("A.DISPATCH_ORDER_CODE AS DISPATCH_ORDER_CODE, ")
                           .append("A.DISPATCH_DATE AS DISPATCH_DATE, ")
                           .append("VEH.VEHICLE_NAME AS VEHICLE_NAME, ")
                           .append("A.START_FACILITY_NAME AS START_FACILITY_NAME, A.END_FACILITY_NAME AS END_FACILITY_NAME, ")
                           .append("A.MAIN_DRIVER AS DRIVER, ")
                           .append("B.FULL_LOAD_DISTANCE AS FULL_LOAD_DISTANCE, ")
                           .append("B.EMPTY_LOAD_DISTANCE AS EMPTY_LOAD_DISTANCE, ")
                           .append("(B.FULL_LOAD_DISTANCE + B.EMPTY_LOAD_DISTANCE) AS ACTUAL_LOAD_DISTANCE, ")
                           .append("B.MAIN_DRIVER_FULL_SALARY AS FULL_SALARY, B.MAIN_DRIVER_EMPTY_SALARY AS EMPTY_SALARY, ")
                           .append("B.MAIN_DRIVER_SALARY AS DRIVER_SALARY ")
                    .append("FROM VW_DISPATCH_ORDER A, TBL_TRUCK_TRANSPORT_COST B, TBL_TRUCK_DISPATCH_ORDER C, VW_TRANSPORTATION_COMPANY D, VW_VEHICLE VEH ")
                    .append("WHERE A.ID = B.DISPATCH_ORDER_ID AND A.DISPATCH_ORDER_CODE = C.DISPATCH_ORDER_CODE AND C.ORGANIZATION_ID = D.ID AND A.VEHICLE_ID = VEH.ID AND A.MAIN_DRIVER <> ' - ' AND VEH.IS_DISABLED = 0 AND A.IS_EXPENSE_REIMBURSED = 1 ")
                    .append(getPreparedFilter(false).clauseSentence() +")")
                 .append("UNION ALL ")
                   .append("(SELECT A.ID, ")
                          .append("A.FIRST_VICE_DRIVER_ID, ")
                          .append("D.ORGANIZATION_NAME, ")
                          .append("A.DISPATCH_ORDER_CODE, ")
                          .append("A.DISPATCH_DATE, ")
                          .append("VEH.VEHICLE_NAME, ")
                          .append("A.START_FACILITY_NAME, ")
                          .append("A.END_FACILITY_NAME, ")
                          .append("A.FIRST_VICE_DRIVER, ")
                          .append("B.FULL_LOAD_DISTANCE, ")
                          .append("B.EMPTY_LOAD_DISTANCE, ")
                          .append("(B.FULL_LOAD_DISTANCE + B.EMPTY_LOAD_DISTANCE), ")
                          .append("B.FIRST_VICE_DRIVER_FULL_SALARY, ")
                          .append("B.FIRST_VICE_DRIVER_EMPTY_SALARY, ")
                          .append("B.FIRST_VICE_DRIVER_SALARY ")
                  .append("FROM VW_DISPATCH_ORDER A, TBL_TRUCK_TRANSPORT_COST B, TBL_TRUCK_DISPATCH_ORDER C, VW_TRANSPORTATION_COMPANY D, VW_VEHICLE VEH ")
                  .append("WHERE A.ID = B.DISPATCH_ORDER_ID AND A.DISPATCH_ORDER_CODE = C.DISPATCH_ORDER_CODE AND C.ORGANIZATION_ID = D.ID AND A.VEHICLE_ID = VEH.ID AND A.FIRST_VICE_DRIVER <> ' - ' AND VEH.IS_DISABLED = 0 AND A.IS_EXPENSE_REIMBURSED = 1 ")
                  .append(getPreparedFilter(false).clauseSentence() +") ")
                .append(") AB" + (driverId != null ? (" WHERE AB.DRIVER_ID = " + driverId) : "")  + ")")
          .append(" GROUP BY ROLLUP (DRIVER, ID)) ");
        
        String sentence = "SELECT COUNT(*) FROM (" + buffer.toString() + ")";
        return sentence;                                 
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(1024);
        
        buffer.append("SELECT * FROM (")
                .append("SELECT ID, ")
                    .append("CASE WHEN (ID IS NULL AND DRIVER IS NOT NULL) THEN '小计' WHEN (ID IS NULL AND DRIVER IS NULL) THEN '总计' ELSE DRIVER END AS driver, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(ORGANIZATION_NAME) END AS organizationName, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(DISPATCH_ORDER_CODE) END AS dispatchOrderCode, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE TO_CHAR(MIN(DISPATCH_DATE), 'YYYY-MM-DD') END AS dispatchDate, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(VEHICLE_NAME) END AS vehicleName, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(START_FACILITY_NAME) END AS startFacilityName, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE MIN(END_FACILITY_NAME) END AS endFacilityName, ")
                    .append("SUM(FULL_LOAD_DISTANCE) AS fullLoadDistance, ")
                    .append("SUM(EMPTY_LOAD_DISTANCE) AS emptyLoadDistance, ")
                    .append("SUM(ACTUAL_LOAD_DISTANCE) AS actualLoadDistance, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE SUM(FULL_SALARY) END AS fullSalary, ")
                    .append("CASE WHEN ID IS NULL THEN NULL ELSE SUM(EMPTY_SALARY) END AS emptySalary, ")
                    .append("SUM(DRIVER_SALARY) AS driverSalary ")
                .append("FROM (SELECT * FROM ( ")
                     .append("(SELECT A.ID, ")
                             .append("A.MAIN_DRIVER_ID AS DRIVER_ID, ")
                             .append("D.ORGANIZATION_NAME AS ORGANIZATION_NAME, ")
                             .append("A.DISPATCH_ORDER_CODE AS DISPATCH_ORDER_CODE, ")
                             .append("A.DISPATCH_DATE AS DISPATCH_DATE, ")
                             .append("VEH.VEHICLE_NAME AS VEHICLE_NAME, ")
                             .append("A.START_FACILITY_NAME AS START_FACILITY_NAME, A.END_FACILITY_NAME AS END_FACILITY_NAME, ")
                             .append("A.MAIN_DRIVER AS DRIVER, ")
                             .append("B.FULL_LOAD_DISTANCE AS FULL_LOAD_DISTANCE, ")
                             .append("B.EMPTY_LOAD_DISTANCE AS EMPTY_LOAD_DISTANCE, ")
                             .append("(B.FULL_LOAD_DISTANCE + B.EMPTY_LOAD_DISTANCE) AS ACTUAL_LOAD_DISTANCE, ")
                             .append("B.MAIN_DRIVER_FULL_SALARY AS FULL_SALARY, B.MAIN_DRIVER_EMPTY_SALARY AS EMPTY_SALARY, ")
                             .append("B.MAIN_DRIVER_SALARY AS DRIVER_SALARY ")
                      .append("FROM VW_DISPATCH_ORDER A, TBL_TRUCK_TRANSPORT_COST B, TBL_TRUCK_DISPATCH_ORDER C, VW_TRANSPORTATION_COMPANY D, VW_VEHICLE VEH ")
                      .append("WHERE A.ID = B.DISPATCH_ORDER_ID AND A.DISPATCH_ORDER_CODE = C.DISPATCH_ORDER_CODE AND C.ORGANIZATION_ID = D.ID AND A.VEHICLE_ID = VEH.ID AND A.MAIN_DRIVER <> ' - ' AND VEH.IS_DISABLED = 0 AND A.IS_EXPENSE_REIMBURSED = 1 ")
                      .append(getPreparedFilter(false).clauseSentence() +")")
                   .append("UNION ALL ")
                     .append("(SELECT A.ID, ")
                             .append("A.FIRST_VICE_DRIVER_ID, ")
                             .append("D.ORGANIZATION_NAME, ")
                             .append("A.DISPATCH_ORDER_CODE, ")
                             .append("A.DISPATCH_DATE, ")
                             .append("VEH.VEHICLE_NAME, ")
                             .append("A.START_FACILITY_NAME, ")
                             .append("A.END_FACILITY_NAME, ")
                             .append("A.FIRST_VICE_DRIVER, ")
                             .append("B.FULL_LOAD_DISTANCE, ")
                             .append("B.EMPTY_LOAD_DISTANCE, ")
                             .append("(B.FULL_LOAD_DISTANCE + B.EMPTY_LOAD_DISTANCE), ")
                             .append("B.FIRST_VICE_DRIVER_FULL_SALARY, ")
                             .append("B.FIRST_VICE_DRIVER_EMPTY_SALARY, ")
                             .append("B.FIRST_VICE_DRIVER_SALARY ")
                    .append("FROM VW_DISPATCH_ORDER A, TBL_TRUCK_TRANSPORT_COST B, TBL_TRUCK_DISPATCH_ORDER C, VW_TRANSPORTATION_COMPANY D, VW_VEHICLE VEH ")
                    .append("WHERE A.ID = B.DISPATCH_ORDER_ID AND A.DISPATCH_ORDER_CODE = C.DISPATCH_ORDER_CODE AND C.ORGANIZATION_ID = D.ID AND A.VEHICLE_ID = VEH.ID AND A.FIRST_VICE_DRIVER <> ' - ' AND VEH.IS_DISABLED = 0 AND A.IS_EXPENSE_REIMBURSED = 1 ")
                    .append(getPreparedFilter(false).clauseSentence() +") ")
                  .append(") AB" + (driverId != null ? (" WHERE AB.DRIVER_ID = " + driverId) : "")  + ")")
            .append(" GROUP BY ROLLUP (DRIVER, ID)) ");
                               
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
        
        return sentence;
        
    }

    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("driver", rs.getObject("driver"));
                result.put("organizationName", rs.getObject("organizationName"));
                result.put("dispatchOrderCode", rs.getObject("dispatchOrderCode"));
                result.put("dispatchDate", rs.getObject("dispatchDate"));
                result.put("vehicleName", rs.getObject("vehicleName"));
                result.put("startFacilityName", rs.getObject("startFacilityName"));
                result.put("endFacilityName", rs.getObject("endFacilityName"));
                result.put("fullLoadDistance", rs.getObject("fullLoadDistance"));
                result.put("emptyLoadDistance", rs.getObject("emptyLoadDistance"));
                result.put("actualLoadDistance", rs.getObject("actualLoadDistance"));
                result.put("fullSalary", rs.getObject("fullSalary"));
                result.put("emptySalary", rs.getObject("emptySalary"));
                result.put("driverSalary", rs.getObject("driverSalary"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the driverId
     */
    public Long getDriverId() {
        return driverId;
    }

    /**
     * @return the beginDispatchDate
     */
    public String getBeginDispatchDate() {
        return beginDispatchDate;
    }

    /**
     * @return the endDispatchDate
     */
    public String getEndDispatchDate() {
        return endDispatchDate;
    }

    /**
     * @return the beginReimburseDate
     */
    public String getBeginReimburseDate() {
        return beginReimburseDate;
    }

    /**
     * @return the endReimburseDate
     */
    public String getEndReimburseDate() {
        return endReimburseDate;
    }

    /**
     * @return the beginIndex
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }

    /**
     * @return the endIndex
     */
    public Integer getEndIndex() {
        return endIndex;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param driverId the driverId to set
     */
    public void setDriverId(Long driverId) {
        this.driverId = driverId;
    }

    /**
     * @param beginDispatchDate the beginDispatchDate to set
     */
    public void setBeginDispatchDate(String beginDispatchDate) {
        this.beginDispatchDate = beginDispatchDate;
    }

    /**
     * @param endDispatchDate the endDispatchDate to set
     */
    public void setEndDispatchDate(String endDispatchDate) {
        this.endDispatchDate = endDispatchDate;
    }

    /**
     * @param beginReimburseDate the beginReimburseDate to set
     */
    public void setBeginReimburseDate(String beginReimburseDate) {
        this.beginReimburseDate = beginReimburseDate;
    }

    /**
     * @param endReimburseDate the endReimburseDate to set
     */
    public void setEndReimburseDate(String endReimburseDate) {
        this.endReimburseDate = endReimburseDate;
    }

    /**
     * @param beginIndex the beginIndex to set
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }

    /**
     * @param endIndex the endIndex to set
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }
    
}
